package com.example.tool.conf;

import cn.hutool.core.util.ObjectUtil;
import com.alibaba.fastjson.JSONObject;
import com.example.tool.erp.sdk.api.sales.dto.TradeQueryResponse;
import com.example.tool.service.RedisService;
import com.example.tool.service.claimsinfo.ClaimsInfoService;
import com.example.tool.util.DDUtil;
import com.example.tool.util.WDTUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;

import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;


@Slf4j
@Component
public class ScheduledTasks {
    @Autowired
    RedisService redisService;
    @Autowired
    ClaimsInfoService claimsInfoService;
    private final String yearsStr = "yyyy-MM";
    private final String dayStr = "yyyy-MM-dd";
    private final Integer readBatchSize = 5000;
    private final Integer updateBatchSize = 5000;
    private final String secret = "";
    private final String token = "";
    private final List<String> atUserIds = Arrays.asList("413803412626577886", "17450514151207813");
    //理赔统计更新区域key
    private final String lptjRangeAddressKey = "sys:lptjRangeAddress:lastIndexKey";
    //理赔统计文档ID
    private final String lptjWorkbookId = "r1R7q3QmWepMDOX0U72oRrLq8xkXOEP2";
    //理赔登记表文档ID
    private final String lpdjbWorkbookId = "ZX6GRezwJlyDpZnPIna9eB5q8dqbropQ";
    //理赔统计更新区域key
    private final String lpdjRangeAddressKey = "sys:lpdjRangeAddress:lastIndexKey";
    //理赔登记表工作表
    private final String lpdjSheetId = "理赔登记";
    /**
     * 每天某个时间点
     * 定时统计 https://alidocs.dingtalk.com/uni-preview?extension=xlsx&bizType=document&cloudSpaceDentryId=162342956522&previewAtta=2&cloudSpaceSpaceId=23223539178&version=28381&scene=universalSpace&mainsiteOrigin=mainsite&spaceId=23223539178&fileSize=2763853&dentryUuid=ndMj49yWjX4ZXrx9ho7PB24jJ3pmz5aA&isMobile=false&fileId=162342956522
     * 最后一条分割线下的数据   在最后一行下补充一个分割线时间
     * 再获取https://alidocs.dingtalk.com/i/nodes/ZgpG2NdyVXaOEALjTQz6NPDQJMwvDqPk?utm_scene=team_space里的昨天工作表的数据 获取后删除工作表
     * 两个数据合并去重 追加到（判断是否存在 如果不存在创建一个工作表） https://alidocs.dingtalk.com/i/nodes/r1R7q3QmWepMDOX0U72oRrLq8xkXOEP2?utm_scene=team_space
     *
     */
    /**
     * 添加统计数据
     */
    @Scheduled(cron = "0 0 0 * * ?", zone = "Asia/Shanghai")
    public void scheduledImportClaims() {
        log.info("---------理赔表格统计数据任务开始---------");
        List<List<String>> values = findClaimSettlement();
        importClaims(values);
        log.info("---------理赔表格统计数据任务结束---------");
    }

    /**
     * 导入数据
     *
     * @param values
     */
    public void importClaims(List<List<String>> values) {
        //
//        //判断是否存在本月的统计数据
//        //不存在创建 补充表头设置最后一条分割线的下标是2
//        //存在 使用查询出来的下标的追加数据
        SimpleDateFormat sdf = new SimpleDateFormat(yearsStr);
        Date date = new Date();
        String yyyyMM = sdf.format(date);
//        sdf=new SimpleDateFormat(dayStr);
//        String yyyyMMdd = sdf.format(date);
//        String workbookId="r1R7q3QmWepMDOX0U72oRrLq8xkXOEP2";
        JSONObject sheets = DDUtil.getSheets(lptjWorkbookId);
//        String tjRangeAddressKey="sys:tjRangeAddress:lastIndexKey";//统计文档最后一条分割线下标
        String sheetId = "";
        Integer lastNonNullIndex = 1;
        List<String> yearsSheets = sheets.getJSONArray("value").stream().map(val -> {
            JSONObject vm = (JSONObject) val;
            return vm.getString("name");
        }).filter(name -> name.equals(yyyyMM)).collect(Collectors.toList());
        if (yearsSheets.isEmpty()) {

            DDUtil.createSheets(lptjWorkbookId, yyyyMM);
            sheetId = yyyyMM;

            List<String> title = Arrays.asList("物流单号", "订单备注", "店铺名称", "日期");
            List<List<String>> titleParam = Collections.singletonList(title);
            String upRangeAddress = "".concat("A").concat(lastNonNullIndex.toString()).concat(":").concat("D").concat(lastNonNullIndex.toString());
            DDUtil.updateRange(lptjWorkbookId, sheetId, upRangeAddress, titleParam);
//            lastNonNullIndex=lastNonNullIndex+1;
//
//            values=new ArrayList<>();
//            values.add(title);
//            values.addAll(storageData);
        } else {
            sheetId = yearsSheets.get(0);
            lastNonNullIndex = optimizeTable(lptjRangeAddressKey, lptjWorkbookId, sheetId);//获取上次最后一条非空数据
        }


        int totalSize = values.size();
        Integer startIndex = lastNonNullIndex;
        Integer endIndex = startIndex;

//        for (int start = 0; start < totalSize; start += updateBatchSize) {
//            int end = Math.min(start + updateBatchSize, totalSize);
//            List<List<Object>>batchValues=values.subList(start, end).stream().map(v->v.stream().map(e-> {
//                Object o = new Object();
//                o=e;
//                return o;
//            }).collect(Collectors.toList()) ).collect(Collectors.toList());
//             lastNonNullIndex = findLastNonNullIndex(batchValues);
////            startIndex=endIndex+1;
////            endIndex=startIndex+batchValues.size()-1;
////            String rangeAddress="".concat("A").concat(startIndex.toString()).concat(":").concat("D").concat(endIndex.toString());
////            DDUtil.updateRange(lptjWorkbookId,sheetId,rangeAddress,batchValues);
//        }


        for (int start = 0; start < totalSize; start += updateBatchSize) {
            int end = Math.min(start + updateBatchSize, totalSize);
            List<List<String>> batchValues = values.subList(start, end);
            startIndex = endIndex + 1;
            endIndex = startIndex + batchValues.size() - 1;
            String rangeAddress = "".concat("A").concat(startIndex.toString()).concat(":").concat("D").concat(endIndex.toString());
            DDUtil.updateRange(lptjWorkbookId, sheetId, rangeAddress, batchValues);
        }


        redisService.set(lptjRangeAddressKey, endIndex.toString());

//        String content=new SimpleDateFormat(dayStr).format(date).concat("理赔数据已处理点击可查看。https://alidocs.dingtalk.com/i/nodes/r1R7q3QmWepMDOX0U72oRrLq8xkXOEP2?utm_scene=team_space");
//        DDUtil.sendMessageWebhookUserIds(secret,token,content,atUserIds);
    }


    /**
     * 处理第一个
     *
     * @param iData
     * @return
     */
    public List<List<String>> getLogistics(List<String> iData) {
//        Date date = new Date();
        Calendar cal = Calendar.getInstance();
        cal.add(Calendar.DATE, -1);
        SimpleDateFormat sdf = new SimpleDateFormat(dayStr);
//        String yyyyMMdd = sdf.format(date);
        String yyyyMMdd = sdf.format(cal.getTime());
        String logistics = iData.get(0);
        String center = iData.get(1);
        List<String> logisticsData = Arrays.stream(processTrackingNo(logistics).split(" ")).filter(e -> StringUtils.isNotBlank(e) && e.length() > 5).map(String::trim).collect(Collectors.toList());
        List<List<String>> lValue = new ArrayList<>();
        for (String lData : logisticsData) {
            List<String> cValue = new ArrayList<>();
            cValue.add(lData);
            cValue.add(center);
            TradeQueryResponse.OrderItem orderItem = WDTUtil.logisticsNoQueryOrder(lData);
            if (ObjectUtil.isNotNull(orderItem)) {
                if (StringUtils.isBlank(center)) {
                    cValue.set(1, orderItem.getCsRemark());
                }
                cValue.add(orderItem.getShopName());
            } else {
                cValue.add("");
            }
            cValue.add(yyyyMMdd);
            lValue.add(cValue);
        }

        return lValue;
    }

    /**
     * 去中文，符号
     */
    public static String processTrackingNo(String logistics) {
        if (logistics.contains("\r") || logistics.contains("\n")) {
            String trim = logistics.toUpperCase().replace("\r", " ").replace("\n", " ").replaceAll("[\\u4e00-\\u9fa5]", "").replaceAll("[\\p{Punct}\\p{IsPunctuation}]", " ");
            return trim;
        }
        return logistics.toUpperCase().replaceAll("[\\u4e00-\\u9fa5]", " ").replaceAll("[\\p{Punct}\\p{IsPunctuation}]", " ");
    }


    /**
     * 获取所有理赔并处理
     */
    public List<List<String>> findClaimSettlement() {
        //获取理赔登记表得数据
        Integer lastDividerLineIndex = optimizeTable(lpdjRangeAddressKey, lpdjbWorkbookId, lpdjSheetId, true);//查询最后一条分割线
//        Integer num=5000;
        Integer startIndex = lastDividerLineIndex + 1;
        Integer endIndex = startIndex + readBatchSize;
        List<List<Object>> oValues = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            String rangeAddress = "A".concat(startIndex.toString()).concat(":").concat("C").concat(endIndex.toString());
            JSONObject rsData = JSONObject.parseObject(DDUtil.readSheets(lpdjbWorkbookId, lpdjSheetId, rangeAddress));
            List<List<Object>> rsoData = rsData.getJSONArray("values").stream().map(v -> {
                List<Object> vs = (List<Object>) v;
                vs.remove(1);
                return vs;
            }).collect(Collectors.toList());
            long rssData = rsoData.stream().filter(v -> StringUtils.isNotBlank(v.get(0).toString())).count();
            if (rssData == 0) {
                break;
            }
            oValues.addAll(rsoData);
            startIndex = endIndex + 1;
            endIndex = startIndex + readBatchSize;
        }
        Integer lastNonNullIndex = findLastNonNullIndex(oValues);
        if (!oValues.isEmpty()) {
            oValues = oValues.subList(0, lastNonNullIndex + 1);
        }
        lastNonNullIndex = lastNonNullIndex + lastDividerLineIndex;
//        redisService.set(lpdjRangeAddressKey,lastNonNullIndex.toString());
        supplementalDividingLines(lastNonNullIndex + 2, lpdjbWorkbookId, lpdjSheetId, Arrays.asList(new SimpleDateFormat(dayStr).format(new Date()), "", ""), lpdjRangeAddressKey);
        List<List<String>> dataValues = oValues.stream().filter(list -> StringUtils.isNotBlank(list.get(0).toString())).map(list -> list.stream()
                .map(Object::toString)
                .collect(Collectors.toList())).collect(Collectors.toList());
//        List<List<String>> collect = dataValues.stream().filter(d -> d.get(0).contains("434618321550724")).collect(Collectors.toList());
//        if(!collect.isEmpty()){
//
//        System.out.println(collect);
//        }
//        List<List<String>> dataValues = oValues.stream()
//        .filter(Objects::nonNull)
//        .map(list -> list.stream()
//        .map(Object::toString)
//        .collect(Collectors.toList()))
//        .collect(Collectors.toList());


        List<List<String>> values = new ArrayList<>();
        for (List<String> dvs : dataValues) {
            List<List<String>> logistics = getLogistics(dvs);
            values.addAll(logistics);
        }
        List<List<String>> remainderData = claimsInfoService.findRemainderData();
        values.addAll(remainderData);
        return deduplicate(values);
    }

    /*    *//**
     * 获取昨日理赔群数据
     * @return
     *//*
    public  List<List<String>> getYesterdayCustomerServiceClaimsData() {
            List<List<String>> resultValues=new ArrayList<>();
//            String workbookId="ZgpG2NdyVXaOEALjTQz6NPDQJMwvDqPk";
            JSONObject sheetsBody = DDUtil.getSheets(lpqWorkbookId);
            JSONArray wValues = sheetsBody.getJSONArray("value");
//            Calendar cal = Calendar.getInstance();
//            cal.add(Calendar.DATE, -1);
//            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//            String yesterday = sdf.format(cal.getTime());
//            List<String> ids = wValues.stream().filter(mv -> {
//            JSONObject nmv = (JSONObject) mv;
//            return nmv.getString("name").equals(yesterday);
//            }).map(mv->{JSONObject nmv = (JSONObject) mv;
//            return nmv.getString("id");}).collect(Collectors.toList());
            List<String> sheetIds=wValues.stream().map(mv->{JSONObject nmv = (JSONObject) mv;
            return nmv.getString("id");}).collect(Collectors.toList());
//            String sheetId="";
//            if (!ids.isEmpty()){
//            sheetId=ids.get(0);
//            }
           for (String sheetId:sheetIds) {
               Long num = 3000L;
               Long startIndex = 2L;
               Long endIndex = startIndex + num + 1;

               for (int i = 0; i < 100; i++) {
                   String rangeAddress = "".concat("A").concat(startIndex.toString()).concat(":").concat("A").concat(endIndex.toString());
                   JSONObject pdBody = JSONObject.parseObject(DDUtil.readSheets(lpqWorkbookId, sheetId, rangeAddress));
                   List<List<String>> pdBodyValues = (List) pdBody.getJSONArray("values");
//                    pdBodyValues = pdBodyValues.stream().map(e -> e.stream().filter(ce ->
//                    StringUtils.isNotBlank(ce) && ce.contains("物流问题/")&& !ce.contains("物流问题/拦截/顾客问题-拦截退回")&&(
//                    ce.contains("处理方案：理赔")||
//                    ce.contains("处理方案：补发")||
//                    ce.contains("处理方案：退差价")||
//                    ce.contains("处理方案：退全款")
//                    )).collect(Collectors.toList())).filter(e ->
//                    e.stream().anyMatch(StringUtils::isNotBlank)).collect(Collectors.toList());


                   if (pdBodyValues.isEmpty()) {//判断结果是否小于1000 是读完了 否未读完
                       break;
                   } else {
                       for (List<String> pdBodyValue : pdBodyValues) {
                           for (String pdbv : pdBodyValue) {
                               resultValues.addAll(dataDispose(pdbv));
                           }
                       }

                   }

                   startIndex = endIndex + 1;
                   endIndex = endIndex + num;

               }

//          DDUtil.delWorkbook(workbookId,sheetId)
           }
        return resultValues;
    }*/

    /**
     * 每行记录元素处理
     *
     * @param val
     * @return
     */
    public static List<List<String>> dataDispose(String val) {
        List<String> texts = Arrays.stream(val.split("；")).map(text -> text.substring(text.indexOf("：") + 1)).collect(Collectors.toList()).subList(1, 3);
        List<List<String>> resultTexts = new ArrayList<>();
        List<String> trackNos = Arrays.stream(texts.get(1).split(",")).collect(Collectors.toList());
        String issue = texts.get(0);
        for (int i = 0; i < trackNos.size(); i++) {
            resultTexts.add(Arrays.asList(trackNos.get(i), issue));
        }
        return resultTexts;
    }


    /**
     * 所有数据去重
     *
     * @param values
     * @return
     */
    public static List<List<String>> deduplicate(List<List<String>> values) {
        Map<String, List<String>> map = new HashMap<>();
        for (List<String> list : values) {
            if (list != null && !list.isEmpty()) {
                String firstElement = list.get(0);
                map.putIfAbsent(firstElement, list);  // 只有当key不存在时才放入
            }
        }
        return new ArrayList<>(map.values());
    }

    /**
     * 获取列表最后一个非空的下标
     *
     * @param listOfLists
     * @return
     */
    public static int findLastNonNullIndex(List<List<Object>> listOfLists) {
        // 1. 防御性编程：处理null或空输入
        if (listOfLists == null || listOfLists.isEmpty()) {
            return -1;
        }

        // 2. 从后向前遍历
        for (int i = listOfLists.size() - 1; i >= 0; i--) {
            List<Object> currentList = listOfLists.get(i);

            // 3. 先检查子列表本身是否为null或空
            if (currentList == null || currentList.isEmpty()) {
                continue;
            }

            // 4. 检查子列表中是否有有效元素（优化版）
            for (Object item : currentList) {
                if (item != null && StringUtils.isNotBlank(item.toString())) {
                    return i; // 找到立即返回
                }
            }
        }

        return -1;
    }

    /**
     * 整理客服理赔记录并获取理赔登记表最后一条分割线的下标
     * String workbookId="Obva6QBXJw6lYwAMFlr3arwxVn4qY5Pr";
     * String sheetId="s1";
     *
     * @param rangeAddressKey
     * @return
     */
    public Integer optimizeTable(String rangeAddressKey, String workbookId, String sheetId) {
        return optimizeTable(rangeAddressKey, workbookId, sheetId, false);
    }

    /**
     * * 整理客服理赔记录并获取理赔登记表最后一条分割线的下标
     * * String workbookId="Obva6QBXJw6lYwAMFlr3arwxVn4qY5Pr";
     * * String sheetId="s1";
     * * @param rangeAddressKey
     *
     * @param rangeAddressKey
     * @param workbookId
     * @param sheetId
     * @param ddDividingLine  true 查询分割线 false查询最后一条非空的下标
     * @return
     */
    public Integer optimizeTable(String rangeAddressKey, String workbookId, String sheetId, Boolean ddDividingLine) {
        String dividerLineIndexStr = redisService.getString(rangeAddressKey);
//        Integer dividerLineIndex=0;//最后一条分割线
        Integer dividerLineIndex = 1;//最后一条分割线
        if (StringUtils.isBlank(dividerLineIndexStr)) {
//            Long num=5000L;
            Long startIndex = 1L;
            Long endIndex = startIndex + readBatchSize;
            List<List<Object>> datas = new ArrayList<>();
            Integer lastNnIndex = 0;
            for (int i = 0; i < 100; i++) {
                String rangeAddress = "A".concat(startIndex.toString()).concat(":").concat("C").concat(endIndex.toString());
                JSONObject rsData = JSONObject.parseObject(DDUtil.readSheets(workbookId, sheetId, rangeAddress));
                List<List<Object>> rsValues = rsData.getJSONArray("values").stream().map(rsd -> (List<Object>) rsd).collect(Collectors.toList());
                Integer nnIndex = findLastNonNullIndex(rsValues);
                if (nnIndex == -1) {
//                nnIndex=findLastNonNullIndex(datas);
                    datas = datas.subList(0, lastNnIndex);
                    break;
                } else {
                    lastNnIndex = datas.size() + nnIndex + 1;
                    datas.addAll(rsValues);
                }
                startIndex = endIndex + 1;
                endIndex = endIndex + readBatchSize;
            }
//            Integer n=0;
//            Integer currentIndex=0;//最后一条数据的下标/执行到那一条
            dividerLineIndex = 0;
            for (int i = 0; i < datas.size(); i++) {
                Object dataOne = datas.get(i).get(0);
                Object dataTwo = datas.get(i).get(1);

                if (ObjectUtil.isNull(dataOne)) {
                    dataOne = new Object();
                }
                if (ObjectUtil.isNull(dataTwo)) {
                    dataTwo = new Object();
                }
                String trackingNoOne = processTrackingNo(dataOne.toString());
                String trackingNoTwo = processTrackingNo(dataTwo.toString());

                if (isExcelDateNumber(trackingNoOne) || isExcelDateNumber(trackingNoTwo)) {
                    dividerLineIndex = i + 1;
                }
            }

            if (!ddDividingLine) {//false 查询最后一条记录
                dividerLineIndex = datas.size();
            }
            redisService.set(rangeAddressKey, dividerLineIndex.toString());
        } else {
            dividerLineIndex = Integer.parseInt(dividerLineIndexStr);
        }
        return dividerLineIndex;
    }

    public static boolean isNumericInteger(String str) {
        if (str == null || str.isEmpty()) {
            return false;
        }
        return str.matches("-?\\d+"); // 匹配可选负号后跟一个或多个数字
    }

    /**
     * 是否是时间分割线
     *
     * @param excelDateNumber
     * @return
     */
    public static Boolean isExcelDateNumber(String excelDateNumber) {
        if (isStrictValidDate(excelDateNumber)) {
            return true;
        }
        return isValidExcelDateNumber(excelDateNumber);
    }


    public static boolean isValidExcelDateNumber(String excelDateNumber) {
        boolean numericInteger = isNumericInteger(excelDateNumber);
        if (!numericInteger) {
            return false;
        }
        if (!(!excelDateNumber.isEmpty() && excelDateNumber.length() <= 7)) {
            return false;
        }
        long excelDateNumberVal = Long.parseLong(excelDateNumber);
        return excelDateNumberVal >= 1 && excelDateNumberVal <= 2958465;
    }

    /**
     * 时间字符串判断
     *
     * @param input
     * @return
     */
    public static boolean isStrictValidDate(String input) {
        if (!isValidDate(input)) {
            return false;
        }

        try {
            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy M d");
            LocalDate.parse(input, formatter);
            return true;
        } catch (DateTimeParseException e) {
            return false;
        }
    }

    // 基础格式验证(同上)
    public static boolean isValidDate(String input) {
        String regex = "^\\d{4}\\s(0?[1-9]|1[0-2])\\s(0?[1-9]|[12][0-9]|3[01])$";
        return input != null && input.matches(regex);
    }

    /**
     * 补充分割线
     *
     * @param lastDividerLineIndex
     * @param workbookId
     * @param sheetId
     * @param values
     * @param rangeAddressKey
     */
    public void supplementalDividingLines(Integer lastDividerLineIndex, String workbookId, String sheetId, List<String> values, String rangeAddressKey) {
        String dividerLineRangeAddress = "".concat("A").concat(lastDividerLineIndex.toString()).concat(":").concat("C").concat(lastDividerLineIndex.toString());
        //#FFA500
        List<List<String>> lists = Collections.singletonList(Arrays.asList("#FFCC99", "#FFB347", "#FF8C00"));
        //values
        DDUtil.updateRange(workbookId, sheetId, dividerLineRangeAddress, Collections.singletonList(values), lists);
        if (StringUtils.isNotBlank(rangeAddressKey)) {
            redisService.set(rangeAddressKey, lastDividerLineIndex.toString());
        }
    }

}
